package com.zhang.publisher.mapper;

import com.zhang.publisher.bean.ProductStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * @title: 商品统计Mapper接口
 * @author: zhang
 * @date: 2022/3/17 14:43
 */


public interface ProductStatsMapper {
    //查询某一天商品的总交易额
    @Select("select sum(order_amount) order_amount from product_stats_2022 where toYYYYMMDD(stt)=#{date}")
    BigDecimal selectGmv(Integer date);


    //根据品牌维度统计某天商品交易额排名
    @Select(" select tm_id,tm_name,sum(order_amount) order_amount " +
            " from product_stats_2022 " +
            " where toYYYYMMDD(stt)=#{date} " +
            " group by tm_id,tm_name " +
            " having order_amount>0 " +
            " order by order_amount desc limit #{limit}")
    List<ProductStats> selectProductStatsByTrademark(@Param("date") Integer date,@Param("limit") Integer limit);


    //根据品牌维度统计某天商品交易额排名
    @Select(" select category3_id,category3_name,sum(order_amount) order_amount " +
            " from product_stats_2022 " +
            " where toYYYYMMDD(stt)=#{date} " +
            " group by category3_id,category3_name " +
            " having order_amount>0 " +
            " order by order_amount desc limit #{limit}")
    List<ProductStats> selectProductStatsByCategory3(@Param("date") Integer date,@Param("limit") Integer limit);


    //根据商品spu维度统计某天商品交易额排名
    @Select(" select spu_id,spu_name,sum(order_amount) order_amount ,sum(order_ct) order_ct" +
            " from product_stats_2022 " +
            " where toYYYYMMDD(stt)=#{date} " +
            " group by spu_id,spu_name " +
            " having order_amount>0 " +
            " order by order_amount desc limit #{limit}")
    List<ProductStats> selectProductStatsBySpu(@Param("date") Integer date,@Param("limit") Integer limit);
}
